﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.OleDb;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class GASstation
    {
        string error = null, String = "",
            selectMSSQL = "";

        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);

        [OperationContract]
        public List<GAS> DoWork(string city)
        {
            string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;

            List<GAS> GASList = new List<GAS>();
            try
            {
                try {
                    conMSSQL = new SqlConnection(connectionStringMSSQL);
                    conMSSQL.Open();
                } catch { error += " Невозможно подключиться к базе данных. MS SQL"; }

                switch (city)
                {
                    case "perm":
                        {
                            //string select = "SELECT POIOFCAT_13.[NO], POIOFCAT_13.XCOORD, POIOFCAT_13.YCOORD, POIOFCAT_13.NAME_ORG, POIOFCAT_13.STREET, POIOFCAT_13.DOM FROM POIOFCAT_13";
                            //string selectMSSQL = "SELECT RATING_GAS_PERM.[ID], RATING_GAS_PERM.RATING FROM RATING_GAS_PERM ORDER BY RATING_GAS_PERM.[ID]";
                            selectMSSQL = "SELECT GAS_PERM.[NO], GAS_PERM.XCOORD, GAS_PERM.YCOORD, GAS_PERM.NAME_ORG, GAS_PERM.STREET, GAS_PERM.DOM, RATING_GAS_PERM.RATING FROM RATING_GAS_PERM INNER JOIN GAS_PERM ON RATING_GAS_PERM.ID = GAS_PERM.[NO]";                            
                            break;
                        }
                    case "ekb":
                        {
                            selectMSSQL = "SELECT GAS_AND_CARWASH_EKB.[NO], GAS_AND_CARWASH_EKB.XCOORD, GAS_AND_CARWASH_EKB.YCOORD, GAS_AND_CARWASH_EKB.NAME, GAS_AND_CARWASH_EKB.STREET, GAS_AND_CARWASH_EKB.DOM, RATING_GAS_EKB.RATING FROM RATING_GAS_EKB INNER JOIN GAS_AND_CARWASH_EKB ON RATING_GAS_EKB.ID = GAS_AND_CARWASH_EKB.[NO]";
                            break;
                        }
                    case "permkray":
                        {
                            
                            break;
                        }
                }  
                
                try {
                    commandMSSQL = new SqlCommand(selectMSSQL, conMSSQL);
                    readerMSSQL = commandMSSQL.ExecuteReader();
                } catch { error += " Невозможно получить список АЗС. MS SQL"; }

                while (readerMSSQL.Read())
                {
                    GAS gas = new GAS();
                    gas.no = readerMSSQL.GetInt32(0);
                    gas.X = Math.Round(readerMSSQL.GetDouble(1), 2);
                    gas.Y = Math.Round(readerMSSQL.GetDouble(2), 2);
                    gas.name = readerMSSQL[3].ToString();
                    gas.adress = readerMSSQL[4].ToString() + " " + readerMSSQL[5].ToString();
                    gas.reit = (readerMSSQL[6].ToString() != "") ? (int)readerMSSQL.GetDouble(6) : 0;

                    GASList.Add(gas);
                }

                if (error != null)
                {
                    switch (city)
                    {
                        case "perm":
                            {
                                String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "ekb":
                            {
                                String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "permkray":
                            {
                                String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                    }
                    try
                    {
                        commandMSSQL = new SqlCommand(String, conMSSQL);
                        commandMSSQL.ExecuteNonQuery();
                    }
                    catch { }
                }
                conMSSQL.Close();
            } catch { }
            return GASList;
        }
        [DataContract]
        public class GAS
        {
            [DataMember]
            public int no = 0;
            [DataMember]
            public double X = 0.0;
            [DataMember]
            public double Y = 0.0;
            [DataMember]
            public string name = "";
            [DataMember]
            public string adress = "";
            [DataMember]
            public int reit = 0;
        }
        // Add more operations here and mark them with [OperationContract]
    }
}
